package dbTools;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.LinkedHashMap;
import java.util.Map;

import org.eclipse.swt.widgets.Shell;

import projectsupporter.common.formbase.progrees.LongRunningOperation;
import projectsupporter.common.logic.GlobalInfo;
import projectsupporter.form.MainForm;

import jxl.Hyperlink;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import dbTools.factory.SimpleFactory;
import dbTools.tableInfo.field.Field;
import dbTools.tableInfo.field.fieldInfo.type.Type;
import dbTools.tableInfo.field.fieldInfo.type.types.TypeReference;
import dbTools.tableInfo.field.fields.DateField;
import dbTools.tableInfo.field.fields.NumberField;
import dbTools.tableInfo.field.fields.StringField;
import dbTools.tableInfo.info.TableInfo;

public class DBToolEngine {

	private String fileName;
	private MainForm form;
	
	public DBToolEngine(String fileName) {
		this.fileName=fileName;
	}

	public static Map<String,TableInfo> TABLE_INFO_MAP;
	private static Map<String,String> TABLE_INFO_KEY;
	
	public final static LinkedHashMap<String,Class> fieldTypes = new LinkedHashMap<String,Class> ();  
    static{  
    	fieldTypes.put("varchar2", StringField.class);  
    	fieldTypes.put("date", DateField.class);  
    	fieldTypes.put("number", NumberField.class);  
    	fieldTypes.put("long", NumberField.class);  
    }
    
    public static TableInfo findTable(String key)
	{
		if(TABLE_INFO_KEY == null || TABLE_INFO_MAP == null)
		{
			return null;	
		}
		
		String tableKey=TABLE_INFO_KEY.get(key);
		
		if(tableKey == null)
		{
			return null;
		}
		
		return TABLE_INFO_MAP.get(tableKey);
	}
    
    public Workbook getWorkBook()
    {
    	TABLE_INFO_MAP = new LinkedHashMap<String,TableInfo>();
		TABLE_INFO_KEY = new LinkedHashMap<String,String>();
		
		InputStream is = null;
        Workbook workbook = null;
        try {

        	File file=new File(fileName);
        	
        	if(file.exists()==false)
        	{
        		return null;
        	}

			is = new FileInputStream(fileName);
			
			WorkbookSettings workbooksetting = new WorkbookSettings();
			workbooksetting .setCellValidationDisabled(true);

			workbook = Workbook.getWorkbook(is,workbooksetting);
			
        }catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (is != null) {
                try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
            }
        }
		return workbook;
    }
    
	public void run()
	{
		TABLE_INFO_MAP = new LinkedHashMap<String,TableInfo>();
		TABLE_INFO_KEY = new LinkedHashMap<String,String>();
		
		InputStream is = null;
        Workbook workbook = null;
        try {

        	File file=new File(fileName);
        	
        	if(file.exists()==false)
        	{
        		return;
        	}

			is = new FileInputStream(fileName);
			
			WorkbookSettings workbooksetting = new WorkbookSettings();
			workbooksetting .setCellValidationDisabled(true);

			workbook = Workbook.getWorkbook(is,workbooksetting);
			
			
			System.out.println("共有" +workbook.getNumberOfSheets() + "sheets");
			
			Sheet mainSheet = workbook.getSheet("テーブル一覧");
			
			if(mainSheet==null){
				return ;
			}
			final Hyperlink[] tableLinks =mainSheet.getHyperlinks();
			
			for(Hyperlink link:tableLinks)
			{
				fillTableInfo(workbook, mainSheet, link);
			}
			
				System.out.println(TABLE_INFO_MAP.size());
				
			workbook.close();
			
		} catch (Exception e) {
            e.printStackTrace(System.out);
        } finally {
            if (is != null) {
                try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
            }
        }
        
		return ;
	}

	public String fillTableInfo(Workbook workbook, Sheet mainSheet,
			 Hyperlink link) {
		   String name="";
			if(link.isLocation()){
				name=mainSheet.getCell(link.getColumn(), link.getRow()).getContents();
				
				Sheet sheet = workbook.getSheet(name);
				
				if(sheet==null)
				{
					System.out.println(name);
				  return name;
				}
				
				//ﾅﾐｶﾏｺﾏｷｨsheet
				if(sheet.getColumns()<7
				   ||sheet.getRows()<0||
				   sheet.getCell(6, 0)==null 
				   || sheet.getCell(6, 0).getContents()==null 
				   || sheet.getCell(6, 0).getContents().equals("【テーブルID】")==false)
				{
					System.out.println(name);
					return name;
				}
				
				//tableInfo
				TableInfo tableInfo=new TableInfo();
				tableInfo.setTableId(sheet.getCell(7, 0).getContents());
				tableInfo.setTableName(sheet.getCell(0, 0).getContents());
				tableInfo.setAllFields(new LinkedHashMap<String,Field>());
				tableInfo.setKeyFields(new LinkedHashMap<String,Field>());
				tableInfo.setFieldsKeyForRef(new LinkedHashMap<String,String>());
				tableInfo.setKey(tableInfo.getTableId().substring(0,1).toUpperCase()+ tableInfo.getTableId().substring(1));
				
				//tableFields
				int totalRows=sheet.getRows();
				int rowIndex=	3;
				while(rowIndex<totalRows && sheet.getCell(2, rowIndex).getContents().trim().length() >0 )
				{
					//番号
					String varNum=sheet.getCell(0, rowIndex).getContents();
					
					//キー
					String varKey=sheet.getCell(1, rowIndex).getContents();
					
					//項目名
					String varItemName=sheet.getCell(2, rowIndex).getContents();
					
					//テーブル定義名
					String varFiledName=sheet.getCell(3, rowIndex).getContents();
					
					//データ型
					String varDataType=sheet.getCell(4, rowIndex).getContents();
					
					//形式
					String varChar=sheet.getCell(5, rowIndex).getContents();
					
					//桁数
					String varLenght=sheet.getCell(6, rowIndex).getContents();
					
					//説明
					String varCon=sheet.getCell(7, rowIndex).getContents();
					
					//必須
					String varNotNull=sheet.getCell(8, rowIndex).getContents();
					
					//ﾃﾞﾌｫﾙﾄ
					String varDefault=sheet.getCell(9, rowIndex).getContents();
					
					//最小値
					String varMin=sheet.getCell(10, rowIndex).getContents();
					
					//最大値
					String varMax=sheet.getCell(11, rowIndex).getContents();
					
					Field field =SimpleFactory.getReflectObj(getFiledType(varDataType),
							varNum,varKey,varItemName,varFiledName,varDataType,varChar,varLenght,varCon,
							varNotNull,varDefault,varMin,varMax);
					if(field.isKey()){
						tableInfo.getKeyFields().put(field.getFieldName(), field);
						tableInfo.getAllFields().put(field.getFieldName(), field);
					}
					else{
						tableInfo.getAllFields().put(field.getFieldName(), field);
					}
					
					tableInfo.getFieldsKeyForRef().put(field.getFieldName(),field.getFieldName());
					tableInfo.getFieldsKeyForRef().put(field.getItemName(),field.getFieldName());
					
					rowIndex++;
				}
				
				if(tableInfo.getKeyFields().size()==0)
				{
					tableInfo.setKeyFields(tableInfo.getAllFields());
					tableInfo.setAllFields(new LinkedHashMap<String,Field>());
				}
				
				TABLE_INFO_MAP.put(tableInfo.getKey(), tableInfo);
				
				TABLE_INFO_KEY.put(tableInfo.getKey(), tableInfo.getKey());
				TABLE_INFO_KEY.put(tableInfo.getTableName(), tableInfo.getKey());
			}
			return name;
			
	}
	
	private static Class getFiledType(String dataType)
	{
		if(DBToolEngine.fieldTypes.containsKey(dataType))
		{
			return DBToolEngine.fieldTypes.get(dataType);
		}
		else
		{
			return StringField.class;
		}
	}
	
	public static TypeReference getTypeReference_REFOnly(Field field)
	{
		 TypeReference ref = null;
		 if(field.getFieldInfo()!=null)
		 {
			 for(Type type: field.getFieldInfo().getType())
			 {
				 if(type instanceof TypeReference)
				 {
					 ref=(TypeReference) type;
					 if(DBToolEngine.findTable(ref.getReferenceId()) == null
						||!ref.getRefrenceType().equals(TypeReference.REF_TYPE_REF))
					 {
						 ref = null;
						 continue;
					 }
					 break;
				 }
			 }
		 }
		 
		 return ref;
	}
	
	private static String eclipseWorkSpacePath;
	public static String getWorkSpacePath()
	{
		if(eclipseWorkSpacePath==null || eclipseWorkSpacePath.trim().length()==0)
		{
			try {
				eclipseWorkSpacePath = new TableInfo().getClass().getResource("/").getPath().replace("/"+GlobalInfo.PLUGIN_ID+"/bin", "").substring(1);
				eclipseWorkSpacePath = java.net.URLDecoder.decode(eclipseWorkSpacePath,"utf-8");
			} catch (Exception e) {
				
			}
		}
		return eclipseWorkSpacePath;
	}
}
